package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.Chance;
import com.sl.au.entity.ExpectedOrder;
import com.sl.au.entity.ExpectedOrderToChance;


@Transactional(readOnly = true)
public interface ExpectedOrderToChanceRepository extends JpaRepository<ExpectedOrderToChance, String> {
	
	@Query(" select a from ExpectedOrderToChance a ")
	Page<ExpectedOrderToChance> findExpectedOrder(String key, Pageable pageable);
	
	@Query(" select count(*) from ExpectedOrderToChance a")
	int getExpectedOrderCount(String key);
	
	@Query(" select a from ExpectedOrderToChance a ")
	Page<ExpectedOrderToChance> findExpectedOrder(Pageable pageable);
	
	@Query(" select count(*) from ExpectedOrderToChance a")
	int getExpectedOrderCount();
	
	
	/**
	 * 查询所有
	 */
	@Query(" select a from ExpectedOrderToChance a where a.echargePerson like '%'+?1+'%' or a.cname like '%'+?1+'%' or a.cuserName like '%'+?1+'%' or a.csuperior like '%'+?1+'%' or a.csourceArea like '%'+?1+'%' or a.cbusiness  like '%'+?1+'%' ")
	Page<ExpectedOrderToChance> findAll(String key, Pageable pageable);
	
	@Query(" select count(1) from ExpectedOrderToChance a where a.echargePerson like '%'+?1+'%' or a.cname like '%'+?1+'%' or a.cuserName like '%'+?1+'%' or a.csuperior like '%'+?1+'%' or a.csourceArea like '%'+?1+'%' or a.cbusiness  like '%'+?1+'%' ")
	long getAllCount(String key);
	
	/**
	 * 本部门
	 */
	@Query(" select a from ExpectedOrderToChance a where (a.echargePerson like '%'+?1+'%' or a.cname like '%'+?1+'%' or a.cuserName like '%'+?1+'%' or a.cbusiness  like '%'+?1+'%'  or a.csourceArea like '%'+?1+'%' )"
			+ " and a.csuperior = ?2 ")
	Page<ExpectedOrderToChance> findSuperiorPage(String key,String superior, Pageable pageable);
	
	@Query(" select count(1) from ExpectedOrderToChance a where (a.echargePerson like '%'+?1+'%' or a.cname like '%'+?1+'%' or a.cuserName like '%'+?1+'%' or a.cbusiness  like '%'+?1+'%'  or a.csourceArea like '%'+?1+'%' )"
			+ " and a.csuperior = ?2 ")
	long getSuperiorCount(String key,String superior);
	
	/**
	 * 本片区
	 */
	@Query(" select a from ExpectedOrderToChance a where (a.echargePerson like '%'+?1+'%' or a.cname like '%'+?1+'%' or a.cuserName like '%'+?1+'%' or a.cbusiness  like '%'+?1+'%'  or a.csuperior like '%'+?1+'%' )"
			+ " and a.csourceArea = ?2 ")
	Page<ExpectedOrderToChance> finAreaPage(String key,String area, Pageable pageable);
	
	@Query(" select count(1) from ExpectedOrderToChance a where (a.echargePerson like '%'+?1+'%' or a.cname like '%'+?1+'%' or a.cuserName like '%'+?1+'%' or a.cbusiness  like '%'+?1+'%'  or a.csuperior like '%'+?1+'%' )"
			+ " and a.csourceArea = ?2 ")
	long getAreaCount(String key,String area);
	
	/**
	 * 个人
	 */
	@Query(" select a from ExpectedOrderToChance a where (a.echargePerson like '%'+?1+'%' or a.cname like '%'+?1+'%' or a.cuserName like '%'+?1+'%' or a.cbusiness  like '%'+?1+'%' )"
			+ " and a.euserId = ?2 ")
	Page<ExpectedOrderToChance> findSelfPage(String key,String userId, Pageable pageable);
	
	@Query(" select count(1) from ExpectedOrderToChance a where (a.echargePerson like '%'+?1+'%' or a.cname like '%'+?1+'%' or a.cuserName like '%'+?1+'%' or a.cbusiness  like '%'+?1+'%' )"
			+ " and a.euserId = ?2 ")
	long getSelfCount(String key,String userId);
	
}
